package dbgroup02.dao.db2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import dbgroup02.bean.BoardingPass;
import dbgroup02.dao.BoardingPassDao;

public class BoardingPassDB2Dao implements BoardingPassDao {

	private JdbcTemplate jdbcTemplate;

	ParameterizedRowMapper<BoardingPass> getBoardingPassMapper = new ParameterizedRowMapper<BoardingPass>() {
		@Override
		public BoardingPass mapRow(ResultSet resultSet, int arg1)
				throws SQLException {
			BoardingPass boardingPass = new BoardingPass();
			boardingPass.setIdBoardingPass(resultSet.getInt(1));
			boardingPass.setBoardingTime(resultSet.getTime(2));
			boardingPass.setSeatNumber(resultSet.getString(3));
			boardingPass.setTerminal(resultSet.getString(4));
			boardingPass.setGate(resultSet.getString(5));
			return boardingPass;
		}
	};

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	public int addBoardingPass(final BoardingPass boardingPass) {
		int newId = 0;

		KeyHolder keyHolder = new GeneratedKeyHolder();
		Object template = jdbcTemplate.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement prepareStatement = con
						.prepareStatement(
								"INSERT INTO "
										+ BoardingPassDao.SCHEMA
										+ ".BOARDING_PASS2 ( BOARDING_TIME, SEAT_NUMBER, TERMINAL, GATE)"
										+ "VALUES (?,?,?,?)",
								Statement.RETURN_GENERATED_KEYS);

				prepareStatement.setTime(1, boardingPass.getBoardingTime());
				prepareStatement.setString(2, boardingPass.getSeatNumber());
				prepareStatement.setString(3, boardingPass.getTerminal());
				prepareStatement.setString(4, boardingPass.getGate());
				;
				return prepareStatement;
			}
		}, keyHolder);
		newId = keyHolder.getKey().intValue();
		return newId;
	}

	@Override
	@Transactional(readOnly = true, propagation = Propagation.REQUIRED)
	public BoardingPass getBoardingPass(int idBoardingPass) {
		List<BoardingPass> result = jdbcTemplate.query("SELECT * FROM "
				+ BoardingPassDao.SCHEMA
				+ ".BOARDING_PASS2 WHERE BOARDING_PASS_ID = ?",
				new Object[] { idBoardingPass }, getBoardingPassMapper);

		if (result.size() > 0)
			return result.get(0);
		else
			return null;
	}

}
